#!c:/perl/bin/perl.exe

use DBI;
use DBD::mysql;
use DIVA_DB;
use WWCOM;
use CGI;
use strict;
    
my $DEBUG= 0;
$DIVA_DB::DEBUG= $DEBUG;

$DIVA_DB::DATABASE_NAME= "wwcom";
$DIVA_DB::DATABASE_SERVER= "localhost:3306";

my $cgi= new CGI;
my %outvars;

my $season= '1997';
my @names= <$WWCOM::SITEBASEDIR/reports/$season/*.html>;
my $fixtures= "$WWCOM::SITEBASEDIR/reports/$season/fixtures.html";

print "$fixtures\n";


#first initialise all competitions and matches in competitions
open FH,"<$fixtures";
my @fixtures= <FH>;
close FH;
my (%comps,%matches);

my $fixt= join('',@fixtures);
my $dbh= &DIVA_DB::DB_CONNECT('root','FineBob+');

#get season id
my ($sth,$rv)= &DIVA_DB::GET_DATA($dbh,$cgi,\%outvars,['SEASON'],['*'],'',"SEASON_NAME='$season'");
if ($rv<=0) {
    print "No matching season - breaking\n";
    $sth->finish;
    exit(0);
}
my $reshash= $sth->fetchrow_hashref;
my $season_id= $reshash->{FK_OBJECT_ID};
$sth->finish;

print "Got seasonID: $season_id\n";

while ($fixt=~ m^<tr>\s*<td>.*?</td>\s*
    <td[\^>]*?>(?:<b>)?([\w\s\-]*?)\:(?:<br>)?([\w\s]*?)(?:</b>)?</td>\s*
    <td[\^>]*?>([\^>]+?)</td>\s*
    <td>([0-9\.\:]{4,5})</td>\s*
    <td>\s*(?:<em>|<b>)\s*
    <a\s+href="javascript\:getThread\('(\d{8})',\s?'reports'\)">([A-Za-z\s\/]+)\s+(\d+),\s+([A-Za-z\s\/]+)\s+(\d+)</a>\s*
    #</em></td>\s*
    ^igsx) {

    print "Match found: $1, $2, $3, $4, $5, $6, $7, $8, $9\n";
    my ($match_name,$opp,$venue,$kotime,$kodate,$home,$homescore,$away,$awayscore)= ($1,$2,$3,$4,$5,$6,$7,$8,$9);

    #get date
    $kotime=~ /(\d+)(?:\.|\:)(\d+)/;
    my ($h,$m)= ($1,$2);
    $kotime= ($h>=12 ? "$h:$m" : ($h+12).":$m");
    $kotime.= ':00';
    $kodate=~ /(\d{4})(\d{2})(\d{2})/;
    my ($match_date)= "$1-$2-$3 $kotime";
    print "KOTime: $match_date\n";
    my ($sth,$rv)= &DIVA_DB::GET_DATA($dbh,$cgi,\%outvars,['MATCHSTATS'],['*'],'',"MATCH_KOTIME='$match_date'");
    if ($rv>0) {
        print "Already a match for this day - skipping\n";
        $sth->finish;
        next;
    }
    $sth->finish;
    
    #no match so we need to add one - need a competition first - take a guess
    my $comp_name= 'Friendlies';  #default to Friendly match    
    if ($match_name=~ /Elimin|Qualif|Play\-off|Playoff|Grand Final/) {  #this is probably a super league playoff match
        $comp_name= 'Super League Playoff Series';
    } elsif ($match_name=~ /Super League/) {  #this is probably a super league match
        $comp_name= 'Super League';
        $match_name=~ /(Round\s+\d+)/;  #remove the comp name
        $match_name= $1;
    } elsif ($match_name=~ /Round|Quarter|Semi|Final/) {  #this is probably a challenge cup match
        $comp_name= 'Challenge Cup';
    }
    print "Competition: $comp_name\n";
    my ($sth,$rv)= &DIVA_DB::GET_DATA($dbh,$cgi,\%outvars,['COMPETITION','OBJECT'],
        ['*'],'',"COMPETITION_NAME='$comp_name' and".
        " FK_PARENT_ID=$season_id and FK_OBJECT_ID=PK_OBJECT_ID");
    if ($rv<=0) {
        print "No appropriate competition for this match - skipping\n";
        $sth->finish;
        next;
    }
    my $reshash= $sth->fetchrow_hashref;
    my $comp_id= $reshash->{FK_OBJECT_ID};
    $sth->finish;
    print "CompetitionID: $comp_id\n";
    
    #groovey - we now need to find the home and away teams!
    my ($sth,$rv)= &DIVA_DB::GET_DATA($dbh,$cgi,\%outvars,['CLUB'],
        ['*'],'',"CLUB_NAME='$home'");
    if ($rv<=0) {
        print "No appropriate home team for this match - skipping\n";
        $sth->finish;
        next;
    }
    my $reshash= $sth->fetchrow_hashref;
    my $home_id= $reshash->{FK_OBJECT_ID};
    $sth->finish;
    print "HomeID: $home_id\n";

    my ($sth,$rv)= &DIVA_DB::GET_DATA($dbh,$cgi,\%outvars,['CLUB'],
        ['*'],'',"CLUB_NAME='$away'");
    if ($rv<=0) {
        print "No appropriate away team for this match - skipping\n";
        $sth->finish;
        next;
    }
    my $reshash= $sth->fetchrow_hashref;
    my $away_id= $reshash->{FK_OBJECT_ID};
    $sth->finish;
    print "AwayID: $away_id\n";
    
    #now we need a squad for each team
    my ($sth,$rv)= &DIVA_DB::GET_DATA($dbh,$cgi,\%outvars,['SQUAD'],
        ['*'],'',"FK_CLUB_ID='$home_id' and FK_SEASON_ID='$season_id'");
    my $homesquad_id= '';
    if ($rv<=0) {
        print "No appropriate home squad for this match - adding\n";
        &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'OBJECT',['FK_CONTENTTYPE_ID','OBJECT_DATE','FK_USER_ID'],
            [[10,'NOW()',12032]]);
        $homesquad_id= $dbh->{'mysql_insertid'};
        &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'SQUAD',['FK_OBJECT_ID','SQUAD_NAME','FK_SEASON_ID','FK_CLUB_ID'],
            [[$dbh->{'mysql_insertid'},'First Team',$season_id,$home_id]]);
        print "\tSquad Inserted!\n\n";        
    } else {
        my $reshash= $sth->fetchrow_hashref;
        $homesquad_id= $reshash->{FK_OBJECT_ID};
    }
    $sth->finish;
    print "HomeSquadID: $homesquad_id\n";
    
    my ($sth,$rv)= &DIVA_DB::GET_DATA($dbh,$cgi,\%outvars,['SQUAD'],
        ['*'],'',"FK_CLUB_ID='$away_id' and FK_SEASON_ID='$season_id'");
    my $awaysquad_id= '';
    if ($rv<=0) {
        print "No appropriate home squad for this match - adding\n";
        &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'OBJECT',['FK_CONTENTTYPE_ID','OBJECT_DATE','FK_USER_ID'],
            [[10,'NOW()',12032]]);
        $awaysquad_id= $dbh->{'mysql_insertid'};
        &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'SQUAD',['FK_OBJECT_ID','SQUAD_NAME','FK_SEASON_ID','FK_CLUB_ID'],
            [[$dbh->{'mysql_insertid'},'First Team',$season_id,$away_id]]);
        print "\tSquad Inserted!\n\n";        
    } else {
        my $reshash= $sth->fetchrow_hashref;
        $awaysquad_id= $reshash->{FK_OBJECT_ID};
    }
    $sth->finish;
    print "AwaySquadID: $awaysquad_id\n";
    
    #now we just need a venue
    my $match_venue= '';
    if ($venue eq 'H' || $venue eq 'A') {
        $match_venue= '__DEFAULT__';
    } else {
        $match_venue= $venue;        
    }
    
    my $match_notes= qq~<a href="$WWCOM::SITEBASEURL/reports/$season/$kodate.html" target="_blank">Go to original summary page</a>~;
    
    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'OBJECT',['FK_CONTENTTYPE_ID','OBJECT_DATE','FK_PARENT_ID','FK_USER_ID'],
        [[7,'NOW()',$comp_id,12032]]);
    my $match_id= $dbh->{'mysql_insertid'};
    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'MATCHSTATS',['FK_OBJECT_ID','MATCH_NAME','MATCH_KOTIME','FK_HOME_ID','FK_AWAY_ID',
    'MATCH_VENUE','MATCH_NOTES','MATCH_HOMESCORE','MATCH_AWAYSCORE',
    'FK_HOMESQUAD_ID','FK_AWAYSQUAD_ID','MATCHSTATS_STATSACTIVE'],
        [[$dbh->{'mysql_insertid'},$match_name,$match_date,$home_id,$away_id,$match_venue,$match_notes,
        $homescore,$awayscore,$homesquad_id,$awaysquad_id,'True']]);
    print "Inserted!\n\n";

    #add teams
    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'OBJECT',['FK_CONTENTTYPE_ID','OBJECT_DATE','FK_PARENT_ID','FK_USER_ID'],
        [[9,'NOW()',$match_id,12032]]);
    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'TEAM',['FK_OBJECT_ID','FK_MATCHSTATS_ID','FK_CLUB_ID'],
        [[$dbh->{'mysql_insertid'},$match_id,$home_id]]);
    print "\tInserted Home Team!\n\n";

    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'OBJECT',['FK_CONTENTTYPE_ID','OBJECT_DATE','FK_PARENT_ID','FK_USER_ID'],
        [[9,'NOW()',$match_id,12032]]);
    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'TEAM',['FK_OBJECT_ID','FK_MATCHSTATS_ID','FK_CLUB_ID'],
        [[$dbh->{'mysql_insertid'},$match_id,$away_id]]);
    print "\tInserted Away Team!\n\n";

}


#my $count= 0;
#for(my $i= 0; $i<$#names; $i++) {
#    open(FH,$names[$i]);
#    my @data= <FH>;
#    close FH;
#    my $data= join('',@data);
#    
#    if ($data=~ /<h2>(.*?)<\/h2>/i) {
#        $$vals[$count][0]= $1;
#    } else {
#        $$vals[$count][0]= '';
#    }
#    if ($data=~ /<div class=player>(.*?)<\/div>/si) {
#        $$vals[$count][1]= $1;
#    } else {
#        $$vals[$count][1]= '';
#    }
#    if ($data=~ /Height: (.*?)\n/i) {
#        $$vals[$count][2]= $1;
#    } else {
#        $$vals[$count][2]= '';
#    }
#    if ($data=~ /Weight: (.*?)\n/i) {
#        $$vals[$count][3]= $1;
#    } else {
#        $$vals[$count][3]= '';
#    }
#    if ($data=~ /Date of Birth: (.*?)\/(.*?)\/(.*?)\n/i) {
#        my $db_date= "$3-$2-$1";
#        $$vals[$count][4]= $db_date;
#    } else {
#        $$vals[$count][4]= '';
#    }
#    if ($data=~ /Place of Birth: (.*?)\n/i) {
#        $$vals[$count][5]= $1;
#    } else {
#        $$vals[$count][5]= '';
#    }
#
#    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'OBJECT',['FK_CONTENTTYPE_ID','OBJECT_DATE'],[[1,'NOW()']]);
#    $$vals[$count][6]= $dbh->{'mysql_insertid'};
#    &DIVA_DB::INSERT_DATA($dbh,$cgi,\%outvars,'PLAYER',$cols,$vals);
#}
#    
